package www.worq.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;

import www.worq.controller.beans.personalBean;

public class personalDao extends oreqDao {

	/** login */
	public void getUsuario(personalBean usuario) throws Exception {
		StringBuilder query = new StringBuilder();

		try {
			query.append("select * from personal Where user_name='")
					.append(usuario.getUsr_name()).append("'");

			conn = this.getConnectionMYSQL();
			ps = conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			System.out.println("dao.usuarios.getUsuario.Ejecutado----------> "
					+ query.toString());

			if (rs.next()) {
				usuario.setId_personal(rs.getInt(1));
				usuario.setMatricula(rs.getString(2));
				usuario.setOficina(rs.getString(3));
				usuario.setNombre(rs.getString(4));
				usuario.setUsr_name(rs.getString(5));
				usuario.setUsr_pass(rs.getString(6));
				usuario.setRol(rs.getString(7));
				usuario.setEmail(rs.getString(8));
				usuario.setDireccion(rs.getString(9));

				System.out
						.println("dao.usuarios.getUsuario----------> Cargado");
			} else {
				throw new Exception("Error usuario no existe.");
			}
		} catch (SQLException error) {
			throw new Exception(error.getMessage());
		} catch (Exception error) {
			throw new Exception(error.getMessage());
		} finally {
			close(conn);
			close(ps);
			close(rs);
		}
	}

	public ArrayList<personalBean> getCatalogo() throws SQLException {
		ArrayList<personalBean> usuariosList = new ArrayList<personalBean>();
		try {
			StringBuilder query = new StringBuilder();
			query.append("Select * from personal order by oficina");
			conn = this.getConnectionMYSQL();
			ps = conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			System.out.println("dao.usuarios.getCatalogo.Ejecutado----------> "
					+ query.toString());

			while (rs.next()) {
				personalBean usuario = new personalBean();
				usuario.setId_personal(rs.getInt(1));
				usuario.setMatricula(rs.getString(2));
				usuario.setOficina(rs.getString(3));
				usuario.setNombre(rs.getString(4));
				usuario.setUsr_name(rs.getString(5));
				usuario.setUsr_pass(rs.getString(6));
				usuario.setRol(rs.getString(7));
				usuario.setEmail(rs.getString(8));
				usuario.setDireccion(rs.getString(9));
				usuariosList.add(usuario);
			}
			System.out.println("dao.usuarios.getCatalogo----------> Cargado");
		} catch (Exception e) {
			System.out.println("usuariosDao...Error..." + e.getMessage());
		} finally {
			close(conn);
			close(ps);
			close(rs);
		}
		return usuariosList;
	}

	public ArrayList<personalBean> getOficina(String oficina)
			throws SQLException {
		ArrayList<personalBean> usuariosList = new ArrayList<personalBean>();
		try {
			StringBuilder query = new StringBuilder();
			query.append("Select * from personal where oficina='")
					.append(oficina).append("'");
			conn = this.getConnectionMYSQL();
			ps = conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			System.out.println("dao.usuarios.getOficina.Ejecutado----------> "
					+ query.toString());

			while (rs.next()) {
				personalBean usuario = new personalBean();
				usuario.setId_personal(rs.getInt(1));
				usuario.setMatricula(rs.getString(2));
				usuario.setOficina(rs.getString(3));
				usuario.setNombre(rs.getString(4));
				usuario.setUsr_name(rs.getString(5));
				usuario.setUsr_pass(rs.getString(6));
				usuario.setRol(rs.getString(7));
				usuario.setEmail(rs.getString(8));
				usuario.setDireccion(rs.getString(9));
				usuariosList.add(usuario);
			}
			System.out.println("dao.usuarios.getOficina----------> Cargado");
		} catch (Exception e) {
			System.out.println("usuariosDao...Error..." + e.getMessage());
		} finally {
			close(conn);
			close(ps);
			close(rs);
		}
		return usuariosList;
	}

	public boolean validarUsuario(personalBean usuario) throws Exception {
		StringBuilder query = new StringBuilder();
		boolean resp = false;

		try {
			query.append("select * from personal Where user_name='")
					.append(usuario.getUsr_name()).append("'");

			conn = this.getConnectionMYSQL();
			ps = conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			System.out
					.println("dao.usuarios.validarUsuario.Ejecutado----------> "
							+ query.toString());

			if (rs.next()) {
				resp = true;
				System.out
						.println("dao.usuarios.validarUsuario----------> encontrado");
			} else {
				resp = false;
			}
		} catch (SQLException error) {
			throw new Exception(error.getMessage());
		} catch (Exception error) {
			throw new Exception(error.getMessage());
		} finally {
			close(conn);
			close(ps);
			close(rs);
		}
		return resp;
	}

	public boolean validarEmail(personalBean usuario) throws Exception {
		StringBuilder query = new StringBuilder();
		boolean resp = false;

		try {
			query.append("select * from personal Where email='")
					.append(usuario.getEmail()).append("'");

			conn = this.getConnectionMYSQL();
			ps = conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			System.out
					.println("dao.usuarios.validarEmail.Ejecutado----------> "
							+ query.toString());

			if (rs.next()) {
				resp = true;
				System.out
						.println("dao.usuarios.validarEmail----------> Encontrado");
			} else {
				resp = false;
			}
		} catch (SQLException error) {
			throw new Exception(error.getMessage());
		} catch (Exception error) {
			throw new Exception(error.getMessage());
		} finally {
			close(conn);
			close(ps);
			close(rs);
		}
		return resp;
	}

	public void actualizarSesion(personalBean usuario) throws Exception {
		StringBuilder query = new StringBuilder();

		try {
			query.append("select * from personal Where id_personal='")
					.append(usuario.getId_personal()).append("'");

			conn = this.getConnectionMYSQL();
			ps = conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			System.out
					.println("dao.usuarios.actualizarSesion.Ejecutado----------> "
							+ query.toString());

			if (rs.next()) {
				usuario.setId_personal(rs.getInt(1));
				usuario.setMatricula(rs.getString(2));
				usuario.setOficina(rs.getString(3));
				usuario.setNombre(rs.getString(4));
				usuario.setUsr_name(rs.getString(5));
				usuario.setUsr_pass(rs.getString(6));
				usuario.setRol(rs.getString(7));
				usuario.setEmail(rs.getString(8));
				usuario.setDireccion(rs.getString(9));
				System.out
						.println("dao.usuarios.actualizarSesion----------> Cargado");
			} else {
				throw new Exception("Error consulta no ejecutada.");
			}
		} catch (SQLException error) {
			throw new Exception(error.getMessage());
		} catch (Exception error) {
			throw new Exception(error.getMessage());
		} finally {
			close(conn);
			close(ps);
			close(rs);
		}
	}

	public void buscarMatricula(personalBean usuario) throws Exception {
		StringBuilder query = new StringBuilder();

		try {
			query.append("select * from personal Where matricula='")
					.append(usuario.getMatricula()).append("'");

			conn = this.getConnectionMYSQL();
			ps = conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			System.out
					.println("dao.usuarios.buscarMatricula.Ejecutado----------> "
							+ query.toString());

			if (rs.next()) {
				usuario.setId_personal(rs.getInt(1));
				usuario.setNombre(rs.getString(5));
				usuario.setUsr_pass(rs.getString(6));
				System.out
						.println("dao.usuarios.buscarMatricula----------> Cargado");
			} else {
				throw new Exception("Error matricula no existe.");
			}
		} catch (SQLException error) {
			throw new Exception(error.getMessage());
		} catch (Exception error) {
			throw new Exception(error.getMessage());
		} finally {
			close(conn);
			close(ps);
			close(rs);
		}
	}

	public int nextId() {
		int id = 0;
		try {
			StringBuilder query = new StringBuilder();
			query.append("Select id_personal from personal");
			conn = this.getConnectionMYSQL();
			ps = conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			System.out.println("dao.usuarios.nextId.Ejecutado----------> "
					+ query.toString());

			while (rs.next()) {
				if (id < rs.getInt(1)) {
					id = rs.getInt(1);
				}
			}
			System.out.println("dao.usuarios.nextId----------> Cargado");
		} catch (Exception e) {
			System.out.println("usuariosDao...Error..." + e.getMessage());
		} finally {
			close(conn);
			close(ps);
			close(rs);
		}
		return id + 1;
	}

	public void insertUsuario(personalBean bean) throws Exception {
		try {
			StringBuilder query = new StringBuilder();
			conn = this.getConnectionMYSQL();

			query.append("INSERT INTO personal ")
					.append("(id_personal,matricula,oficina,nombre,user_name,user_pass,rol,email,direccion) ")
					.append("values (").append(bean.getId_personal())
					.append(",'").append(bean.getMatricula()).append("','")
					.append(bean.getOficina()).append("','")
					.append(bean.getNombre()).append("','")
					.append(bean.getUsr_name()).append("','")
					.append(bean.getUsr_pass()).append("','")
					.append(bean.getRol()).append("','")
					.append(bean.getEmail()).append("','")
					.append(bean.getDireccion()).append("');");

			st = conn.createStatement();
			st.executeUpdate(query.toString());
			System.out
					.println("dao.usuarios.insertUsuario.Ejecutado----------> "
							+ query.toString());
			System.out
					.println("dao.usuarios.insertUsuario----------> insertado");

		} catch (SQLException error) {
			throw new Exception(error.getMessage());
		} catch (Exception error) {
			throw new Exception(error.getMessage());
		} finally {
			close(conn);
			close(st);
		}
	}

	public void deleteUsuario(personalBean bean) throws Exception {
		try {
			StringBuilder query = new StringBuilder();
			conn = this.getConnectionMYSQL();

			query.append("DELETE FROM personal where id_personal=").append(
					bean.getId_personal());

			st = conn.createStatement();
			st.executeUpdate(query.toString());
			System.out
					.println("dao.usuarios.deleteUsuario.Ejecutado----------> "
							+ query.toString());
			System.out
					.println("dao.usuarios.deleteUsuario----------> eliminado");

		} catch (SQLException error) {
			throw new Exception(error.getMessage());
		} catch (Exception error) {
			throw new Exception(error.getMessage());
		} finally {
			close(conn);
			close(st);
		}
	}

	public void updateUsuario(personalBean bean) throws Exception {
		try {
			StringBuilder query = new StringBuilder();
			conn = this.getConnectionMYSQL();

			query.append("UPDATE personal SET ").append("matricula='")
					.append(bean.getMatricula()).append("',oficina='")
					.append(bean.getOficina()).append("',nombre='")
					.append(bean.getNombre()).append("',user_name='")
					.append(bean.getUsr_name()).append("',user_pass='")
					.append(bean.getUsr_pass()).append("',rol='")
					.append(bean.getRol()).append("',email='")
					.append(bean.getEmail()).append("',direccion='")
					.append(bean.getDireccion()).append("' where id_personal=")
					.append(bean.getId_personal()).append(";");

			st = conn.createStatement();
			st.executeUpdate(query.toString());
			System.out
					.println("dao.usuarios.updateUsuario.Ejecutado----------> "
							+ query.toString());
			System.out
					.println("dao.usuarios.updateUsuario----------> actualizado");

		} catch (SQLException error) {
			throw new Exception(error.getMessage());
		} catch (Exception error) {
			throw new Exception(error.getMessage());
		} finally {
			close(conn);
			close(st);
		}
	}

	public void updatePass(personalBean bean) throws Exception {
		try {
			StringBuilder query = new StringBuilder();
			conn = this.getConnectionMYSQL();

			query.append("UPDATE personal SET ").append("user_pass='")
					.append(bean.getUsr_pass()).append("' where id_personal=")
					.append(bean.getId_personal()).append(";");

			st = conn.createStatement();
			st.executeUpdate(query.toString());
			System.out.println("dao.usuarios.updatePASS.Ejecutado----------> "
					+ query.toString());
			System.out
					.println("dao.usuarios.updatePASS----------> actualizado");

		} catch (SQLException error) {
			throw new Exception(error.getMessage());
		} catch (Exception error) {
			throw new Exception(error.getMessage());
		} finally {
			close(conn);
			close(st);
		}
	}
}
